package sapling.dao;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import sapling.model.Batch;
import sapling.model.Tree;

import java.util.List;

/**
 * Created by ever on 2016/10/9.
 */
@Transactional
public interface BatchDao extends JpaRepository<Batch, Long> {
    @Query(value = "select * from batch where month=?1 and standard like ?2 and location like ?3 and height=?4 and chestbreadth=?5 and crownwidth=?6",nativeQuery = true)
    List<Batch> findOnebatch(int month,String standard,String location,double heigh,double Chestbreadth,double crownwidth);

//    @Query(value = "select count()")
//    int finddienumber(Long batch_id);

    @Query(value = "SELECT * from batch WHERE id in (SELECT batch_id FROM tree where batch_id is not NULL )",nativeQuery = true)
    List<Batch> findHavebatch();

    @Query(value = "SELECT * from batch WHERE id not in (SELECT batch_id FROM tree where batch_id is not NULL)",nativeQuery = true)
    List<Batch> findNullbatch();

}
